/*
 * Gets the text, userid, screenname, id, create date and location
 * from the status table
 */

package crypto;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
//import java.util.Calendar;
import java.util.Date;
import java.util.List;
//import java.util.logging.Level;
//import java.util.logging.Logger;
//import javax.swing.JOptionPane;
//import java.io.Serializable;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;



/**
 *
 * @author edi
 */
public class DBQuery {
    private Date begin;
    private String beginString;
    private Date end;
    private String endString;
    private MySQLConnect connection;
    private DateFormat df;
    private Calendar cal;

    //query stuff
//    String stmtWhat = "SELECT `Status`.`Text`, `Status`.`UserId`, `User`.`ScreenName`, `Status`.`Id`, `Status`.`CreatedAt`, `User`.`Location` \n";
//    String stmtFrom = "FROM `Status` \n";
//    String stmtJoin = "LEFT JOIN `User` ON `Status`.`UserId`=`User`.`Id` \n";

    String stmtWhat = "SELECT `status`.`Text`, `status`.`userId`, `user`.`ScreenName`, `status`.`Id`, `status`.`CreatedAt`, `user`.`Location` \n";
    String stmtFrom = "FROM `status` \n";
    String stmtJoin = "LEFT JOIN `user` ON `status`.`UserId`=`user`.`Id` \n";
    String stmtWhere1; //to be filled out when the date range changes
    String stmt1; //to be filled out when the date changes

    //creates a query with given credential and covering the last 24 hours
    public DBQuery(String url, String dbName, String userName, String password, Calendar cal){
        this(url, dbName, userName, password, null, null, cal);
        this.setTodayRange();
    }

    //creates a query object with a given credentials and date range
    public DBQuery(String url, String dbName, String userName, String password, Date begin, Date end, Calendar cal){
        connection = new MySQLConnect(url, dbName, userName, password);
        connection.connectToDB();
        this.cal = cal;

        df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if(begin != null && end != null)
            this.setDateRange(begin, end);
    }

    //sets the current time to the last 24 hours
    public final void setTodayRange(){
        Date e = new Date();
        Date b = new Date(e.getTime() - 24l * 3600l * 1000l);
        this.setDateRange(b, e);
    }

    //sets the date range for the query
    public final void setDateRange(Date begin, Date end){
        this.begin = begin;
        this.end = end;

        this.beginString = df.format(begin);
        this.endString = df.format(end);
        //stmtWhere1 = "WHERE `Status`.`CreatedAt` BETWEEN '" + beginString +"' AND '" + endString + "' \n";    //IO
        //IO - Experimented with this and it retrived records from the database
        stmtWhere1 = "WHERE  `status`.`CreatedAt` < '" + beginString + "' AND  `status`.`CreatedAt` > '" + endString + "'\n";   //IO
        stmt1 = stmtWhat + stmtFrom + stmtJoin + stmtWhere1;
    }

    //runs a query for tweets matching either text or location
    public List<Tweet> queryTweetTextAndLocation(String what){
        //need to deal with quotes
        what = what.replace("'", "\\'");
        String stmtWhere2 = " AND (MATCH (`Status`.`Text`) AGAINST ('\""+what+"\"' IN BOOLEAN MODE) OR \n"
                          + "  MATCH (`User`.`Location`) AGAINST ('\""+what+"\"' IN BOOLEAN MODE));";

       return queryTweetImpl(stmtWhere2);
    }

    //runs a query for tweets matching text only
    public List<Tweet> queryTweetText(String what){
        String stmtWhere2 = " AND MATCH (`Status`.`Text`) AGAINST ('\""+what+"\"' IN BOOLEAN MODE);";

       return queryTweetImpl(stmtWhere2);
    }

    //runs a query for tweets mathing location only
    public List<Tweet> queryTweetLocation(String what){
         String stmtWhere2 = " AND MATCH (`User`.`Location`) AGAINST ('\""+what+"\"' IN BOOLEAN MODE);";

       return queryTweetImpl(stmtWhere2);
    }

    private List<Tweet> queryTweetImpl(String stmtWhere2){
        ArrayList<Tweet> res = new ArrayList<Tweet>();

        String stmt = stmt1; //+ stmtWhere2;
        //System.out.println(stmt);

        ResultSet rs = null;

        try{
            //IO - Just setting a limit to test the query
            stmt = stmt + "LIMIT 0,50";
            rs = connection.runSQLStatement(stmt);
            //printResultSet(rs); //IO for checking result set - comment out during real run
            while(rs.next()){
                TweetImpl t = new TweetImpl();

                t.setText(rs.getString(1));
                t.setFromUserId(rs.getInt(2));
                t.setFromUser(rs.getString(3));
                t.setId(rs.getLong(4));
                Calendar c = (Calendar) cal.clone();
                Date createdAt = df.parse(rs.getString(5));
                c.setTime(createdAt);
                t.setCreatedAt(c);
                t.setLocation(rs.getString(6));

                //System.out.println(t.getText());

                res.add(t);
            }
        }
        catch(Exception e){
            e.printStackTrace();
        }

        return res;
    }


    /**
     * IO Just to test this DB connection
     * @param stmt
     * @throws SQLException
     */
    public void testQuery(String stmt) throws SQLException {
        ResultSet rs = connection.runSQLStatement(stmt);
        printResultSet(rs);
    }


    /**
       *  IO Just to print out resultset
     * @param rs
     * @throws SQLException
     */
    public void printResultSet(ResultSet rs) throws SQLException {
        if(rs==null)
            return;
        int columnCount = rs.getMetaData().getColumnCount();
        String s = "";
        for(int i=1; i<=columnCount; i++) {
            s += rs.getMetaData().getColumnLabel(i) + "|";
        }
        System.out.println(s);

        while(rs.next()) {
            for(int i=1; i<=columnCount; i++) {
                s = new String(rs.getBytes(i));
                 System.out.print(s + "|");
            }
            System.out.println();
        }
    }


    public static void main(String args[]){
        String url = "jdbc:mysql://144.167.34.111:3306/";
        String dbName = "twitterdatacopy";
        String DB_UserName = "cecilia";
        String DB_Password = "cecilia123";
        Calendar cal = Calendar.getInstance();
        //setting being and end time to match your dataset
        cal.set(Calendar.YEAR, 2011);
        cal.set(Calendar.MONTH, 0);
        cal.set(Calendar.DAY_OF_MONTH, 10);
        Date begin = cal.getTime();
        Date end = new Date(begin.getTime() - 3 * 24 * 3600 * 1000);

        //IO with this constructor you can set your own beging and end time
        DBQuery db = new DBQuery(url, dbName, DB_UserName, DB_Password, begin, end, cal);


        //IO I commented out this constructor -  it calculates time range using current time as reference.
        //DBQuery db = new DBQuery(url, dbName, DB_UserName, DB_Password, cal);

        //IO I commented out this constructor -  it calculates time range using current time as reference.
        //DBQuery db = new DBQuery(url, dbName, DB_UserName, DB_Password, cal);
        
//       // try {
//         //   db.testQuery("SELECT * FROM `status` LIMIT 0,50");
//        } catch (SQLException ex) {
//            Logger.getLogger(DBQuery.class.getName()).log(Level.SEVERE, null, ex);
//        }


//        try {
//            db.testQuery("SELECT * FROM `Status` LIMIT 0,50");
//        } catch (SQLException ex) {
//            Logger.getLogger(DBQuery.class.getName()).log(Level.SEVERE, null, ex);
//        }





        List<Tweet> res = db.queryTweetTextAndLocation("");
        for(Tweet t: res){
            System.out.println(t.getLocation()+"\t"+t.getText());
        }

        System.out.println(res.size());
    }
    public String getDateRange() {
        return begin.getTime() + "." + end.getTime() ;
    }
}
